package com.zzedu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zzedu.domain.OrderSumRenewVO;
import com.zzedu.domain.ZzStuRenewOrder;
import com.zzedu.dto.OrderSumDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * (教育-学生)学生续费订单Mapper接口
 *
 * @author Mr.Wang
 * @date 2025-08-16
 */
public interface ZzStuRenewOrderMapper extends BaseMapper<ZzStuRenewOrder> {
    @Select({
            "<script>",
            "SELECT",
            "b.id,",
            "   b.school_name AS schoolName,",
            "   b.contacts,",
            "   b.tel,",
            "   a.renew_type AS renewType,",
            "   COUNT( CASE WHEN a.order_type = 1 THEN a.stu_id END) AS addStuNum,",
            "  round(ifnull(SUM(CASE WHEN a.order_type = 1 THEN a.order_amount END),0),2) AS addStuAmount,",
            "   COUNT( CASE WHEN a.order_type = 2 THEN a.stu_id END) AS renewStuNum,",
            "  round( ifnull(SUM(CASE WHEN a.order_type = 2 THEN a.order_amount END),0),2) AS renewStuAmount,",
            "   round(ifnull(SUM(a.order_amount),0),2) AS orderAmount,",
            "   COUNT( a.stu_id) AS stuNum",
            "FROM zz_stu_renew_order a",
            "LEFT JOIN zz_school b ON a.school_id = b.id",
            "WHERE b.deleted = 0 and a.valid_flag!=4",
            "and exists(select 1 from zz_student c where c.deleted=0 and c.id=a.stu_id)",
            "   <if test='query != null'>",
            "       <if test='query.startTime != null and query.startTime != \"\"'>",
            "           AND a.gmt_create >= #{query.startTime}",
            "       </if>",
            "       <if test='query.endTime != null and query.endTime != \"\"'>",
            "           AND a.gmt_create &lt;= #{query.endTime}",
            "       </if>",
            "       <if test='query.schoolIdList != null and query.schoolIdList.size() > 0'>",
            "           AND a.school_id IN",
            "           <foreach collection='query.schoolIdList' item='id' open='(' separator=',' close=')'>",
            "               #{id}",
            "           </foreach>",
            "       </if>",
            "       <if test='query.calcMonth != null and query.calcMonth != \"\"'>",
            "           AND a.calc_month like concat('%',  #{query.calcMonth}, '%'))",
            "       </if>",
            "       <if test='query.payState != null and query.payState != \"\"'>",
            "           AND a.pay_state =  #{query.payState}",
            "       </if>",
            "       <if test='query.payOrderId != null and query.payOrderId.size() > 0'>",
            "           AND c.id IN ",
            "           <foreach collection='query.payOrderId' item='id' open='(' separator=',' close=')'>",
            "               #{id}",
            "           </foreach>",
            "       </if>",
            "   </if>",
            "GROUP BY a.school_id, a.renew_type  order by a.school_id,a.renew_type",
            "</script>"
    })
    IPage<OrderSumRenewVO> orderSumList(@Param("page") IPage<OrderSumRenewVO> page,
                                        @Param("query") OrderSumDTO query);


    @Select({
            "<script>",
            "SELECT",
            "c.id as billId,",
            "b.id,",
            "   b.school_name AS schoolName,",
            "   b.contacts,",
            "   b.tel,",
            "   a.renew_type AS renewType,",
            "   c.calc_month AS calcMonth,",
            "   c.pay_state AS payState,",
            "   COUNT( CASE WHEN a.order_type = 1 THEN a.stu_id END) AS addStuNum,",
            "  round(ifnull(SUM(CASE WHEN a.order_type = 1 THEN a.order_amount END),0),2) AS addStuAmount,",
            "   COUNT( CASE WHEN a.order_type = 2 THEN a.stu_id END) AS renewStuNum,",
            "  round( ifnull(SUM(CASE WHEN a.order_type = 2 THEN a.order_amount END),0),2) AS renewStuAmount,",
            "   round(ifnull(SUM(a.order_amount),0),2) AS orderAmount,",
            "   COUNT( a.stu_id) AS stuNum",
            "FROM zz_stu_renew_order a",
            "LEFT JOIN zz_school b ON a.school_id = b.id",
            "INNER JOIN zz_charge_order c on c.id=a.pay_charge_id",
            "WHERE b.deleted = 0 and c.deleted=0  and a.valid_flag!=4",
            "and exists(select 1 from zz_student c where c.deleted=0 and c.id=a.stu_id)",
            "   <if test='query != null'>",
            "       <if test='query.startTime != null and query.startTime != \"\"'>",
            "           AND a.gmt_create >= #{query.startTime}",
            "       </if>",
            "       <if test='query.endTime != null and query.endTime != \"\"'>",
            "           AND a.gmt_create &lt;= #{query.endTime}",
            "       </if>",
            "       <if test='query.schoolIdList != null and query.schoolIdList.size() > 0'>",
            "           AND a.school_id IN",
            "           <foreach collection='query.schoolIdList' item='id' open='(' separator=',' close=')'>",
            "               #{id}",
            "           </foreach>",
            "       </if>",
            "       <if test='query.calcMonth != null and query.calcMonth != \"\"'>",
            "           AND a.calc_month like concat('%',  #{query.calcMonth}, '%'))",
            "       </if>",
            "       <if test='query.payState != null and query.payState != \"\"'>",
            "           AND a.pay_state =  #{query.payState}",
            "       </if>",
            "       <if test='query.payOrderId != null and query.payOrderId.size() > 0'>",
            "           AND c.id IN ",
            "           <foreach collection='query.payOrderId' item='id' open='(' separator=',' close=')'>",
            "               #{id}",
            "           </foreach>",
            "       </if>",
            "   </if>",
            "GROUP BY a.school_id, a.renew_type,c.calc_month,c.pay_state,c.id order by a.school_id,a.renew_type",
            "</script>"
    })
    IPage<OrderSumRenewVO> orderSumListPayOrder(@Param("page") IPage<OrderSumRenewVO> page,
                                                @Param("query") OrderSumDTO query);


    @Select({
            "<script>",
            "SELECT",
            "   a.renew_type AS renewType,",
            "   date_format(a.gmt_create, '%Y-%m') AS calcMonth,",
            "   COUNT( CASE WHEN a.order_type = 1 THEN a.stu_id END) AS addStuNum,",
            "  round(ifnull(SUM(CASE WHEN a.order_type = 1 THEN a.order_amount END),0),2) AS addStuAmount,",
            "   COUNT( CASE WHEN a.order_type = 2 THEN a.stu_id END) AS renewStuNum,",
            "  round( ifnull(SUM(CASE WHEN a.order_type = 2 THEN a.order_amount END),0),2) AS renewStuAmount,",
            "   round(ifnull(SUM(a.order_amount),0),2) AS orderAmount,",
            "   COUNT(a.stu_id) AS stuNum",
            "FROM zz_stu_renew_order a",
            "WHERE a.deleted=0  and a.valid_flag!=4",
            "and exists(select 1 from zz_student c where c.deleted=0 and c.id=a.stu_id)",
            "   <if test='query != null'>",
            "       <if test='query.startTime != null and query.startTime != \"\"'>",
            "           AND a.gmt_create >= #{query.startTime}",
            "       </if>",
            "       <if test='query.endTime != null and query.endTime != \"\"'>",
            "           AND a.gmt_create &lt;= #{query.endTime}",
            "       </if>",
            "       <if test='query.schoolIdList != null and query.schoolIdList.size() > 0'>",
            "           AND a.school_id IN",
            "           <foreach collection='query.schoolIdList' item='id' open='(' separator=',' close=')'>",
            "               #{id}",
            "           </foreach>",
            "       </if>",
            "       <if test='query.calcMonth != null and query.calcMonth != \"\"'>",
            "           AND a.calc_month like concat('%',  #{query.calcMonth}, '%'))",
            "       </if>",
            "       <if test='query.payState != null and query.payState != \"\"'>",
            "           AND a.pay_state =  #{query.payState}",
            "       </if>",
            "       <if test='query.payOrderId != null and query.payOrderId.size() > 0'>",
            "           AND a.pay_charge_id IN ",
            "           <foreach collection='query.payOrderId' item='id' open='(' separator=',' close=')'>",
            "               #{id}",
            "           </foreach>",
            "       </if>",
            "   </if>",
            "GROUP BY a.renew_type,date_format(a.gmt_create, '%Y-%m') order by date_format(a.gmt_create, '%Y-%m'),a.renew_type",
            "</script>"
    })
    IPage<OrderSumRenewVO> orderSumListPayOrderToMonth(@Param("page") IPage<OrderSumRenewVO> page,
                                                @Param("query") OrderSumDTO query);
}
